package com.bj58.groupbuy.action.util;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * @author houzuoxin
 *  excel2007工具类
 * */
public class ExcelUtilNew {
	private ReflectUtil reflectUtil = new ReflectUtil();
	public static void downLoadExcel(HttpServletRequest request,
			HttpServletResponse response, String fileName, SXSSFWorkbook wb) {
		try {
			fileName = URLEncoder.encode(fileName, "UTF-8");
			response.setContentType("application/x-download");
			response.addHeader("Content-disposition", "attachment; filename="
					+ new String(fileName.getBytes("GBK"), "ISO-8859-1")
					+ ".xlsx");
			response.setContentType("application/vnd.ms-excel");//;charset=UTF8
			OutputStream ouputStream = response.getOutputStream();
			wb.write(ouputStream);
			ouputStream.flush();
			ouputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	//通用报表下载
	public static <T> SXSSFWorkbook writeExcelCommonForIR(String excelname,
			HttpServletResponse response, List<String> titles,
			List<String> rsList, String pathName) {
		try {
			SXSSFWorkbook wwb = new SXSSFWorkbook(100000);
			/*System.out.println("this one execl down!!!");*/
			XSSFCellStyle cellStyle2 =(XSSFCellStyle) wwb.createCellStyle();
			cellStyle2.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
			XSSFCellStyle cellStyle1 =(XSSFCellStyle) wwb.createCellStyle();
	        cellStyle1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
	        XSSFCellStyle cellStyle =(XSSFCellStyle) wwb.createCellStyle();
	        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
			Map<Integer, List<String>> rsMap = new HashMap<Integer, List<String>>();
			int sheetMaxNum = 1000000;
			if (rsList.size() > sheetMaxNum) {
				int sheetCount = rsList.size() / sheetMaxNum;
				if (rsList.size() % sheetMaxNum > 0) {
					sheetCount++;
				}
				for (int i = 0; i < sheetCount; i++) {
					int beginIndex = i * sheetMaxNum;
					int endIndex = (i + 1) * sheetMaxNum;
					if (endIndex > rsList.size()) {
						endIndex = rsList.size();
					}
					rsMap.put(i, rsList.subList(beginIndex, endIndex));
				}
			} else {
				rsMap.put(0, rsList);
			}

			Iterator<Integer> rsKey = rsMap.keySet().iterator();
			int sheetNum=0;
			int rownum=0;
			while (rsKey.hasNext()) {
				sheetNum = rsKey.next();
				rownum=0;
				List<String> subrsList = rsMap.get(sheetNum);
				String sheetName = "";
				if (sheetNum == 0) {
					sheetName ="sheet" ;//excelname
				} else {
					sheetName = "sheet" + "_" + sheetNum;
				}
				//创建sheet对象
				Sheet ws = wwb.createSheet(sheetName);
				Row row = ws.createRow(rownum++);
				for (int i = 0; i < titles.size(); i++) {
					String ttitleString=titles.get(i).substring(0,titles.get(i).indexOf(":"));
					Cell cell = row.createCell(i);
					cell.setCellValue(ttitleString);
				}//here
				int type=0,i=0,j=0;
				for (i = 0; i < subrsList.size(); i++) {
					String temp = subrsList.get(i);
					String[] value = temp.split(";");
				    row =  ws.createRow(rownum);
					for (j = 0; j < value.length; j++) {
						String result = "";
						try {
							result = value[j].split(",")[0];
							type = Integer.parseInt(value[j].split(",")[1]);
							Cell cell = row.createCell(j);
							cell.setCellValue(result);
							switch (type) {
							case 1: // 整型
								if ("--".equals(result)) {
									result = "0";
								}
								cell.setCellValue(Double.valueOf(result));
							    cell.setCellStyle(cellStyle2);
								break;
							case 2: // 浮点
								if ("--".equals(result)) {
									result = "0";
								}
								cell.setCellValue(Double.valueOf(result));
						        cell.setCellStyle(cellStyle1);
								break;
							case 3: // 百分比
								if ("--".equals(result)) {
									cell.setCellValue(result);
								} else {
									cell.setCellValue(Double.parseDouble(result));
							        cell.setCellStyle(cellStyle);
								}
								break;

							case 4: // 长数字（无需千分位隔开处理）
								if ("--".equals(result)) {
									result = "0";
								}		
								cell.setCellValue(Double.valueOf(result));
								break;
							default:
								cell.setCellValue(result);
								break;
							}
						} catch (Exception e) {
							e.printStackTrace();
							continue;
						}
					}
					rownum++;
				}
			}
			return wwb;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 智能通用报表excel下载,含日比，周比
	 * @return
	 */
	public static <T> SXSSFWorkbook writeExcelCommonForIRWithRatio(
			String excelname, HttpServletResponse response,
			List<String> titles, List<String> rsList,
			HashMap<String, String> keyValue, int gbCount, String pathName,
			boolean dayRatio, boolean weekRatio,int statgrpindex) {
		
		try {
			SXSSFWorkbook wwb = new SXSSFWorkbook();
			XSSFCellStyle cellStyle2 =(XSSFCellStyle) wwb.createCellStyle();
			cellStyle2.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
			XSSFCellStyle cellStyle1 =(XSSFCellStyle) wwb.createCellStyle();
	        cellStyle1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
	        XSSFCellStyle cellStyle =(XSSFCellStyle) wwb.createCellStyle();
	        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
			Map<Integer, List<String>> rsMap = new HashMap<Integer, List<String>>();
			int sheetMaxNum = 1000000;
			if (rsList.size() > sheetMaxNum) {
				int sheetCount = rsList.size() / sheetMaxNum;
				if (rsList.size() % sheetMaxNum > 0) {
					sheetCount++;
				}
				for (int i = 0; i < sheetCount; i++) {
					int beginIndex = i * sheetMaxNum;
					int endIndex = (i + 1) * sheetMaxNum;
					if (endIndex > rsList.size()) {
						endIndex = rsList.size();
					}
					rsMap.put(i, rsList.subList(beginIndex, endIndex));
				}
			} else {
				rsMap.put(0, rsList);
			}

			Iterator<Integer> rsKey = rsMap.keySet().iterator();
			while (rsKey.hasNext()) {
				int sheetNum = rsKey.next();
				int rownum=0;
				List<String> subrsList = rsMap.get(sheetNum);
				String sheetName = "";
				if (sheetNum == 0) {
					sheetName = excelname;
				} else {
					sheetName = excelname + "_" + sheetNum;
				}
				//创建sheet对象
				SXSSFSheet ws =  (SXSSFSheet) wwb.createSheet(sheetName);
				SXSSFRow row = (SXSSFRow) ws.createRow(rownum++);
				int col = 0;
				int countadd=0;
				for (int i = 0; i < titles.size(); i++) {
					String title = titles.get(i);
					String ttitleString=titles.get(i).substring(0,titles.get(i).indexOf(":"));
					Cell cell = row.createCell(i+countadd);
					cell.setCellValue(ttitleString);
					int type = Integer.parseInt(title.substring(title
							.indexOf(":") + 1,title.indexOf(":")+2));
					if (type != 0) {
						if (dayRatio) {
							countadd++;
							Cell cell1 = row.createCell(i+countadd);
							cell1.setCellValue("日环比");
						}
						if (weekRatio) {
							countadd++;
							Cell cell1 = row.createCell(i+countadd);
							cell1.setCellValue("周同比");
						}
					}
				}//here
				for (int i = 0; i < subrsList.size(); i++) {
					row = (SXSSFRow) ws.createRow(rownum);
					String temp = subrsList.get(i);
					String[] value = temp.split(";");
					String currentDate = value[statgrpindex].split(",")[0];
					String keyOfYestoday="";
			    	String keyOfLastWeek="";
			    	String Yestodaystr=DateUtil.strToStrBefore(currentDate,"yyyy-MM-dd", 1);
			    	String LastWeekstr=DateUtil.strToStrBefore(currentDate,"yyyy-MM-dd", 7);
					for (int k = 0; k < gbCount; k++) {
						if(k!=statgrpindex)//如果不是日期
			    		{
							keyOfYestoday += value[k].split(",")[0];
							keyOfLastWeek += value[k].split(",")[0];
							if (k + 1 < gbCount) {
								keyOfYestoday += "_";
								keyOfLastWeek += "_";
							}
			    		}else{
			    			keyOfYestoday+=Yestodaystr;
			    			keyOfLastWeek+=LastWeekstr;
			    			if(k+1<gbCount){
				    			keyOfYestoday+="_";
				        		keyOfLastWeek+="_";
				    		}
			    		}
					}
					col = 0;
					countadd=0;
					for (int j = 0; j < value.length; j++) {
						String result = "";
						try {
							SXSSFCell cell = (SXSSFCell) row.createCell(j+countadd);
							result = value[j].split(",")[0];
							int type = Integer.parseInt(value[j].split(",")[1]);
							switch (type) {
							case 1: // 整型
								if ("--".equals(result)) {
									result = "0";
								}
								cell.setCellValue(Double.valueOf(result));
							    cell.setCellStyle(cellStyle2);
								break;
							case 2: // 浮点
								if ("--".equals(result)) {
									result = "0";
								}
								cell.setCellValue(Double.valueOf(result));
						        cell.setCellStyle(cellStyle1);
								break;
							case 3: // 百分比
								if ("--".equals(result)) {
									cell.setCellValue(result);
								} else {
									cell.setCellValue(Double.parseDouble(result));
							        cell.setCellStyle(cellStyle);
								}
								break;

							case 4: // 长数字（无需千分位隔开处理）
								if ("--".equals(result)) {
									result = "0";
								}		
								cell.setCellValue(Double.valueOf(result));
								break;
							default:
								cell.setCellValue(result);
								break;
							}
							if (type != 0) {
								if (dayRatio)
								{
									countadd++;
									Cell cell1 = row.createCell(j+countadd);
									
									String result1 =comRatio(keyValue,
											keyOfYestoday, result, gbCount, j,
											col++, i + 1);
									if ("--".equals(result1)) {
										cell1.setCellValue(result1);
									} else {									
										cell1.setCellValue(Double.parseDouble(result1));
								        cell1.setCellStyle(cellStyle);
									}
								}
								if (weekRatio)
								{   countadd++;
									Cell cell1 = row.createCell(j+countadd);
									String result2 =comRatio(keyValue,
											keyOfLastWeek, result, gbCount, j,
											col++, i + 1);
									if ("--".equals(result2)) {
										cell1.setCellValue(result2);
									} else {									
										cell1.setCellValue(Double.parseDouble(result2));
								        cell1.setCellStyle(cellStyle);
									}
								}
							}
						} catch (Exception e) {
							e.printStackTrace();
							continue;
						}
					}
					rownum++;
				}
				
			}
			return wwb;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 智能通用报表excel下载同步方法,含日比，周比
	 * @return
	 */
	public synchronized static <T> SXSSFWorkbook writeExcelCommonWithRatioSync(
			String excelname, HttpServletResponse response,
			List<String> titles, List<String> rsList,
			HashMap<String, String> keyValue, int gbCount, String pathName,
			boolean dayRatio, boolean weekRatio,int statgrpindex) {
		try {
			if (keyValue == null)
				return writeExcelCommonForIR(excelname, null, titles, rsList,
						pathName);
			SXSSFWorkbook wwb = new SXSSFWorkbook();
			XSSFCellStyle cellStyle2 =(XSSFCellStyle) wwb.createCellStyle();
			cellStyle2.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
			XSSFCellStyle cellStyle1 =(XSSFCellStyle) wwb.createCellStyle();
	        cellStyle1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
	        XSSFCellStyle cellStyle =(XSSFCellStyle) wwb.createCellStyle();
	        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
			Map<Integer, List<String>> rsMap = new HashMap<Integer, List<String>>();
			int sheetMaxNum = 1000000;
			if (rsList.size() > sheetMaxNum) {
				int sheetCount = rsList.size() / sheetMaxNum;
				if (rsList.size() % sheetMaxNum > 0) {
					sheetCount++;
				}
				for (int i = 0; i < sheetCount; i++) {
					int beginIndex = i * sheetMaxNum;
					int endIndex = (i + 1) * sheetMaxNum;
					if (endIndex > rsList.size()) {
						endIndex = rsList.size();
					}
					rsMap.put(i, rsList.subList(beginIndex, endIndex));
				}
			} else {
				rsMap.put(0, rsList);
			}
			Iterator<Integer> rsKey = rsMap.keySet().iterator();
			while (rsKey.hasNext()) {
				int sheetNum = rsKey.next();
				int rownum=0;
				List<String> subrsList = rsMap.get(sheetNum);
				String sheetName = "";
				if (sheetNum == 0) {
					sheetName = excelname;
				} else {
					sheetName = excelname + "_" + sheetNum;
				}
				//创建sheet对象
				SXSSFSheet ws =  (SXSSFSheet) wwb.createSheet(sheetName);
				SXSSFRow row = (SXSSFRow) ws.createRow(rownum++);
				int col = 0;
				int countadd=0;
				for (int i = 0; i < titles.size(); i++) {
					String title = titles.get(i);
					String ttitleString=titles.get(i).substring(0,titles.get(i).indexOf(":"));
					Cell cell = row.createCell(i+countadd);
					cell.setCellValue(ttitleString);
					int type = Integer.parseInt(title.substring(title
							.indexOf(":") + 1,title.indexOf(":")+2));
					if (type != 0) {
						if (dayRatio) {
							countadd++;
							Cell cell1 = row.createCell(i+countadd);
							cell1.setCellValue("日环比");
						}
						if (weekRatio) {
							countadd++;
							Cell cell1 = row.createCell(i+countadd);
							cell1.setCellValue("周同比");
						}
					}
				}//here
				for (int i = 0; i < subrsList.size(); i++) {
					row = (SXSSFRow) ws.createRow(rownum);
					String temp = subrsList.get(i);
					String[] value = temp.split(";");
					String currentDate = value[statgrpindex].split(",")[0];
					String keyOfYestoday="";
			    	String keyOfLastWeek="";
			    	String Yestodaystr=DateUtil.strToStrBefore(currentDate,"yyyy-MM-dd", 1);
			    	String LastWeekstr=DateUtil.strToStrBefore(currentDate,"yyyy-MM-dd", 7);
					for (int k = 0; k < gbCount; k++) {
						if(k!=statgrpindex)//如果不是日期
			    		{
							keyOfYestoday += value[k].split(",")[0];
							keyOfLastWeek += value[k].split(",")[0];
							if (k + 1 < gbCount) {
								keyOfYestoday += "_";
								keyOfLastWeek += "_";
							}
			    		}else{
			    			keyOfYestoday+=Yestodaystr;
			    			keyOfLastWeek+=LastWeekstr;
			    			if(k+1<gbCount){
				    			keyOfYestoday+="_";
				        		keyOfLastWeek+="_";
				    		}
			    		}
					}
					col = 0;
					countadd=0;
					for (int j = 0; j < value.length; j++) {
						String result = "";
						try {
							SXSSFCell cell = (SXSSFCell) row.createCell(j+countadd);
							result = value[j].split(",")[0];
							int type = Integer.parseInt(value[j].split(",")[1]);
							switch (type) {
							case 1: // 整型
								if ("--".equals(result)) {
									result = "0";
								}
								cell.setCellValue(Double.valueOf(result));
							    cell.setCellStyle(cellStyle2);
								break;
							case 2: // 浮点
								if ("--".equals(result)) {
									result = "0";
								}
								cell.setCellValue(Double.valueOf(result));
						        cell.setCellStyle(cellStyle1);
								break;
							case 3: // 百分比
								if ("--".equals(result)) {
									cell.setCellValue(result);
								} else {
									cell.setCellValue(Double.parseDouble(result));
							        cell.setCellStyle(cellStyle);
								}
								break;

							case 4: // 长数字（无需千分位隔开处理）
								if ("--".equals(result)) {
									result = "0";
								}		
								cell.setCellType(0);
								cell.setCellValue(Double.valueOf(result));
								break;
							default:
								cell.setCellValue(result);
								break;
							}
							if (type != 0) {
								if (dayRatio)
								{
									countadd++;
									Cell cell1 = row.createCell(j+countadd);
									
									String result1 =comRatio(keyValue,
											keyOfYestoday, result, gbCount, j,
											col++, i + 1);
									if ("--".equals(result1)) {
										cell1.setCellValue(result1);
									} else {									
										cell1.setCellValue(Double.parseDouble(result1));
								        cell1.setCellStyle(cellStyle);
									}
								}
								if (weekRatio)
								{   countadd++;
									Cell cell1 = row.createCell(j+countadd);
									String result2 =comRatio(keyValue,
											keyOfLastWeek, result, gbCount, j,
											col++, i + 1);
									if ("--".equals(result2)) {
										cell1.setCellValue(result2);
									} else {									
										cell1.setCellValue(Double.parseDouble(result2));
								        cell1.setCellStyle(cellStyle);
									}
								}
							}
						} catch (Exception e) {
							e.printStackTrace();
							continue;
						}
					}
					rownum++;
				}
				
			}
			return wwb;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

    public static List<String> readCsv(String filePath, String charsetName)
	   throws Exception {
		List<String> rsList = new ArrayList<String>();
		FileInputStream fis = new FileInputStream(filePath);
		InputStreamReader in = new InputStreamReader(fis, charsetName);
		BufferedReader reader = new BufferedReader(in);// 换成你的文件名
		String line = null;
		
		reader.read();
		while ((line = reader.readLine()) != null) {
			if ("GB2312".equals(charsetName)) {
				line = line.replace(",", "@");
			} else if ("unicode".equals(charsetName)) {
				line = line.replaceAll("\\t", "@");
			}
			String check = line.toString().replace("@", "").trim();
			if ("".equals(check)) { // 无数据不放入
				continue;
			}
			rsList.add(line);
         }
        return rsList;
      }


	/**
	 * 把VOLIST 转为 字符串+数据类型的形式 供写入EXCEL
	 * 
	 * @param <T>
	 * @param clazz
	 * @param rsList
	 * @param percentList
	 *            百分比字段List
	 * @param tranCols
	 *            需要转换的字段集合 为NULL则为VO全部字段
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("static-access")
	public <T> List<String> tranVoToString(Class<T> clazz, List<T> rsList,
			List<String> percentList, List<String> tranCols) throws Exception {
		List<String> finalList = new ArrayList<String>();

		Field[] fs = clazz.getDeclaredFields();// CLAZZ所有字段
		List<String> fdNameList = new ArrayList<String>();// 最终需转换的字段名集合
		if (null == tranCols) { // 默认全部字段
			for (Field fd : fs) {
				String fdName = fd.getName();
				fdNameList.add(fdName);
			}
		} else {
			fdNameList = tranCols;
		}

		for (T vo : rsList) {
			StringBuffer values = new StringBuffer();
			for (String fdName : fdNameList) {
				// 字段数据
				Object nowData = reflectUtil.getGetterMethod(clazz,
						clazz.getDeclaredField(fdName)).invoke(vo);
				// 字段数据类型
				Field field = clazz.getDeclaredField(fdName);
				Class<?> filedCls = field.getType();
				int colType = 0; // 数据类型 0 :字符串 1: 整型 2：浮点 3：百分比
				if (filedCls == int.class || filedCls == Integer.class
						|| filedCls == long.class || filedCls == Long.class) {
					colType = 1;
				} else if (filedCls == double.class || filedCls == Double.class) {
					if (null != percentList && percentList.contains(fdName)) {
						colType = 3;
					} else {
						colType = 2;
					}
				} else if (fdName.contains("_hb") || fdName.contains("_tb")) { // 已经转换为字符串类型的
																				// 百分比数据
																				// 环比/同比
					if (null == nowData) {
						nowData = "0%";
					}
					String temp = nowData.toString();
					temp = temp.substring(0, temp.indexOf("%"));
					double tempValue = Double.valueOf(temp) / 100;
					nowData = tempValue;
					colType = 3;
				}

				if ("userId".equals(fdName)) {
					colType = 0;
				}

				if (null != nowData) {
					values.append(nowData.toString() + "," + colType + ";");
				} else {
					values.append("--" + ",0;");
				}
			}

			if (values.toString().split(";").length > 1) {
				finalList.add(values.toString());
			}
		}
		return finalList;
	}

	/**
	 * Excel值写入
	 * 
	 * @param filedCls
	 *            字段类别
	 * @param ws
	 *            Excel表写入对象
	 * @param value
	 *            值
	 * @param isRate
	 *            是否为百分比 0 : 否 1：是
	 * @param col
	 * @param row
	 * @return
	 * @throws Exception
	 */
	private static boolean addExcelValue(Class<?> filedCls, SXSSFCell ws,
			String value, int isRate,SXSSFWorkbook wwb) throws Exception {
		XSSFCellStyle cellStyle =(XSSFCellStyle) wwb.createCellStyle();
		try {
			if (isRate == 1) {
				ws.setCellValue(Double.valueOf(value) / 100);
			} else {// 整型短数字
				if (filedCls == Integer.class || filedCls == int.class) {
				 ws.setCellValue(Double.valueOf(value));
				} else if (filedCls == Double.class || filedCls == double.class) {// 小数点
				 ws.setCellValue(Double.valueOf(value));
			     cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
			     ws.setCellStyle(cellStyle);
				} else if (filedCls == String.class) {
					 ws.setCellValue(value);
				} else if (filedCls == Long.class || filedCls == long.class) { // 整型长数字
					if (value.length() > 15) {// 超过15位则用字符格式
						 ws.setCellValue(value);
					} else {
						ws.setCellValue(Double.valueOf(value));
					    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
					    ws.setCellStyle(cellStyle);
					}
				}
			}
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	
	public static List<String> readExcel(String filePath) throws Exception {
		if (filePath.contains("csv")) { // CSV文件
			return readCsv(filePath, "GB2312");
		}
		InputStream fs = new FileInputStream(filePath);
		List<String> list = new ArrayList<String>();
		Workbook workBook = null;
		if (filePath.endsWith(".xls")) {
			workBook = new HSSFWorkbook(fs);
		}
		else if (filePath.endsWith(".xlsx")) {
			workBook = new XSSFWorkbook(fs);
		}
		// getSheets() 方法返回 Sheet[] 数组 然后利用数组来操作。
		for(int m=0;m<workBook.getNumberOfSheets();m++)
		{
			Sheet sheet= workBook.getSheetAt(m);// 获取所有SHEET
		
			for (Row row : sheet) {
				StringBuffer sb = new StringBuffer();
				for (Cell cell : row) {
					String result="";
					switch (cell.getCellType()) {  
					      case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型  
					           if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
					                SimpleDateFormat sdf = null;  
					                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat  
					                        .getBuiltinFormat("h:mm")) {  
					                    sdf = new SimpleDateFormat("HH:mm");  
					                } else {// 日期  
					                    sdf = new SimpleDateFormat("yyyy-MM-dd");  
					                }  
					                Date date = cell.getDateCellValue();  
					                result = sdf.format(date);  
					            } else if (cell.getCellStyle().getDataFormat() == 58) {  
					                // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
					                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
					                double value = cell.getNumericCellValue();  
					                Date date = org.apache.poi.ss.usermodel.DateUtil  
					                        .getJavaDate(value);  
					                result = sdf.format(date);  
					            } else {  
					            	 double value = cell.getNumericCellValue();    
					                 DecimalFormat format = new DecimalFormat("0.000");  
					                 result = format.format(value);   
					            }  
					            break;  
					        case HSSFCell.CELL_TYPE_STRING:// String类型  
					            result = cell.getRichStringCellValue().toString();  
					            break;  
					        case HSSFCell.CELL_TYPE_BLANK:  
					            result = "";  
					        default:  
					            result = "";  
					            break;  
					        }  
				    /*cell.setCellType(cell.);*///先转换为string，再导入
					sb.append(result);
					sb.append("@");// 将单元格的每行内容隔开
				}
				String check = sb.toString().replace("@", "").trim();
				if ("".equals(check)) { // 无数据不放入
					continue;
				}
				list.add(sb.toString().substring(0,
				sb.toString().lastIndexOf("@")));// 将每行的字符串用一个String类型的集合保存。
			}
			
		}
		return list;
	}
	
	public static List<String> readExcel(InputStream fs,String filename) throws Exception {
		List<String> list = new ArrayList<String>();
		Workbook workBook = null;
		if (filename.endsWith(".xls")) {
			workBook = new HSSFWorkbook(fs);
		}
		else if (filename.endsWith(".xlsx")) {
			workBook = new XSSFWorkbook(fs);
		}
		// getSheets() 方法返回 Sheet[] 数组 然后利用数组来操作。
		for(int m=0;m<workBook.getNumberOfSheets();m++)
		{
			Sheet sheet= workBook.getSheetAt(m);// 获取所有SHEET
		
			for (Row row : sheet) {
				StringBuffer sb = new StringBuffer();
				for (Cell cell : row) {
					String result="";
					switch (cell.getCellType()) {  
					      case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型  
					           if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
					                SimpleDateFormat sdf = null;  
					                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat  
					                        .getBuiltinFormat("h:mm")) {  
					                    sdf = new SimpleDateFormat("HH:mm");  
					                } else {// 日期  
					                    sdf = new SimpleDateFormat("yyyy-MM-dd");  
					                }  
					                Date date = cell.getDateCellValue();  
					                result = sdf.format(date);  
					            } else if (cell.getCellStyle().getDataFormat() == 58) {  
					                // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
					                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
					                double value = cell.getNumericCellValue();  
					                Date date = org.apache.poi.ss.usermodel.DateUtil  
					                        .getJavaDate(value);  
					                result = sdf.format(date);  
					            } else {  
					                double value = cell.getNumericCellValue();    
					                DecimalFormat format = new DecimalFormat("0.000");  
					                result = format.format(value);  
					            }  
					            break;  
					        case HSSFCell.CELL_TYPE_STRING:// String类型  
					            result = cell.getRichStringCellValue().toString();  
					            break;  
					        case HSSFCell.CELL_TYPE_BLANK:  
					            result = "";  
					        default:  
					            result = "";  
					            break;  
					        }  
				    /*cell.setCellType(cell.);*///先转换为string，再导入
					sb.append(result);
					sb.append("@");// 将单元格的每行内容隔开
				}
				String check = sb.toString().replace("@", "").trim();
				if ("".equals(check)) { // 无数据不放入
					continue;
				}
				list.add(sb.toString().substring(0,
				sb.toString().lastIndexOf("@")));// 将每行的字符串用一个String类型的集合保存。
			}
			
		}
		return list;
	}

	/**
	 * 联盟报表excel下载
	 * 
	 * @param <T>
	 * @param excelname
	 *            表格名称
	 * @param response
	 * @param titles
	 *            报表列标题
	 * @param rsList
	 *            查询结果的vo集合
	 * @param Class
	 *            <T> vo clazz
	 * @return
	 */
	@SuppressWarnings("finally")
	public static <T> SXSSFWorkbook writeExcelCommon(String excelname,
			HttpServletResponse response, String[] titles, List<T> rsList,
			Class<T> clazz, String pathName) {
		try {
			SXSSFWorkbook wwb = new SXSSFWorkbook();
			Map<Integer, List<T>> rsMap = new HashMap<Integer, List<T>>();
			int sheetMaxNum = 1000000;
			if (rsList.size() > sheetMaxNum) {
				int sheetCount = rsList.size() / sheetMaxNum;
				if (rsList.size() % sheetMaxNum > 0) {
					sheetCount++;
				}
				for (int i = 0; i < sheetCount; i++) {
					int beginIndex = i * sheetMaxNum;
					int endIndex = (i + 1) * sheetMaxNum;
					if (endIndex > rsList.size()) {
						endIndex = rsList.size();
					}
					rsMap.put(i, rsList.subList(beginIndex, endIndex));
				}
			} else {
				rsMap.put(0, rsList);
			}
			Iterator<Integer> rsKey = rsMap.keySet().iterator();
			while (rsKey.hasNext()) {
				int sheetNum = rsKey.next();
				int rownum=0;
				List<T> subrsList = rsMap.get(sheetNum);
				String sheetName = "";
				if (sheetNum == 0) {
					sheetName = excelname;
				} else {
					sheetName = excelname + "_" + sheetNum;
				}
				//创建sheet对象
				SXSSFSheet ws =  (SXSSFSheet) wwb.createSheet(sheetName);
				SXSSFRow row = (SXSSFRow) ws.createRow(rownum++);
				for (int i = 0; i < titles.length; i++) {
					Cell cell = row.createCell(i);
					cell.setCellValue(titles[i]);
				}
				// 写内容
				Method[] mm = clazz.getMethods();
				Map<String, Method> methodMap = new HashMap<String, Method>();
				for (Method m : mm) {
					methodMap.put(m.getName(), m);
				}

				Field[] fields = clazz.getDeclaredFields();
				for (int i = 0; i < subrsList.size(); i++) {
					T vo = subrsList.get(i);
					row = (SXSSFRow) ws.createRow(rownum);
					int ind = 0;
					for (int j = 0; j < fields.length; j++) {
						Class<?> filedCls = fields[j].getType();// 字段类型
						int isRate = 0;// 是否为百分比 默认不是
						SXSSFCell cell = (SXSSFCell) row.createCell(ind);
						String value = "";
						String methodName = "get"
								+ fields[j].getName().substring(0, 1)
										.toUpperCase()
								+ fields[j].getName().substring(1);
						if (clazz.getName().equals(
								"com.bj58.groupbuy.action.model.ItemMetrics")) { // 新团购项目报表下载需要中间跳过几个字段项
							if (methodName.equals("getStat_date")
									|| methodName.equals("getBusiness_license")) {
								continue;
							}
						}

						if (clazz
								.getName()
								.equals("com.bj58.groupbuy.action.model.CompanyMetricsHour")) { // 新分公司报表下载需要中间跳过几个字段项
							if (methodName.equals("getStat_date")
									|| methodName.equals("getStat_hour")
									|| methodName
											.equals("getConsume_commission")
									|| methodName.equals("getSerialVersionUID")) {
								continue;
							}
						}

						if (clazz
								.getName()
								.equals("com.bj58.groupbuy.action.model.AdCityMetricsHour")
								|| clazz.getName()
										.equals("com.bj58.groupbuy.action.model.AdCityMetricsDay")) { // 广告渠道下载
																										// 从city_name字段后开始忽略

							if (methodName.equals("getCity_name")) {
								break;
							}
						}

						if (clazz
								.getName()
								.equals("com.bj58.groupbuy.action.model.KeywordMetrics")) { // 搜索关键词
							String jude = titles[0];
							if ("日期".equals(jude)) {
								if (methodName.equals("getKeyword")) {
									continue;
								}
								if (methodName.equals("getStat_hour")) {
									break;
								}
							} else if ("关键词".equals(jude)) {
								if (methodName.equals("getStat_date")) {
									continue;
								}
								if (methodName.equals("getExit_count")) {
									break;
								}
							}

						}
						if (clazz.getName().equals(
								"com.bj58.groupbuy.action.model.SHOrderStat")) { // 二手订单统计从order_count字段后开始忽略
							if (methodName.equals("getOrder_count")) {
								break;
							}
						}

						if (clazz
								.getName()
								.equals("com.bj58.groupbuy.action.model.FinanceCheckIn")) {
							if (methodName.equals("getCreate_time")) {
								break;
							}
						}

						if (clazz.getName().equals(
								"com.bj58.groupbuy.action.model.ADClickStat")) {
							if (methodName.equals("getEnt_id")) {
								break;
							}
						}

						if (clazz
								.getName()
								.equals("com.bj58.groupbuy.action.model.FinanceCheckOut")) {
							if (methodName.equals("getPmcid")) {
								value = methodMap.get("getWork_id").invoke(vo)
										.toString()
										+ "-"
										+ methodMap.get("getWork_type")
												.invoke(vo).toString();
								cell.setCellValue(value);
								ind++;
								continue;
							}
							if (methodName.equals("getWork_id")) {
								break;
							}
						}

						if (clazz.getName().equals(
								"com.bj58.groupbuy.action.model.ADClickStat")) {
							if (methodName.equals("getShop_type")) {
								value = methodMap.get(methodName).invoke(vo)
										.toString();
								if ("0".equals(value)) {
									value = "免费";
								} else if ("1".equals(value)) {
									value = "付费";
								}
								cell.setCellValue(value);
								ind++;
								continue;
							}
							if (methodName.equals("getEnt_id")) {
								break;
							}
						}

						if (clazz.getName().equals(
								"com.bj58.groupbuy.action.model.CSTaskStats")) {
							if (methodName.equals("getDone_rate")) {
								DecimalFormat df = new DecimalFormat("0.00");
								value = df
										.format(Double.valueOf(methodMap
												.get(methodName).invoke(vo)
												.toString()) * 100)
										+ "%";
								cell.setCellValue(value);
								ind++;
								continue;
							}
						}

						if (clazz
								.getName()
								.equals("com.bj58.groupbuy.action.model.CSFirsttaskStats")) {
							if (methodName.contains("rate")) {
								DecimalFormat df = new DecimalFormat("0.00");
								value = df
										.format(Double.valueOf(methodMap
												.get(methodName).invoke(vo)
												.toString()) * 100)
										+ "%";
								cell.setCellValue(value);
								ind++;
								continue;
							}
							if (methodName.equals("getCs_userId")) {
								break;
							}
						}

						if (clazz
								.getName()
								.equals("com.bj58.groupbuy.action.model.CSSecondtaskStats")) {
							if (methodName.contains("rate")) {
								DecimalFormat df = new DecimalFormat("0.00");
								value = df
										.format(Double.valueOf(methodMap
												.get(methodName).invoke(vo)
												.toString()) * 100)
										+ "%";
								cell.setCellValue(value);
								ind++;
								continue;
							}
							if (methodName.equals("getDone_byfirst")) {
								break;
							}
						}
						if (clazz.getName().equals(
								"com.bj58.groupbuy.action.model.SalerMetrics")) {
							if (methodName.equals("getGroupbuy_id")
									|| methodName.equals("getItem_name")
									|| methodName.equals("getIs_new_partner")) {
								if (null == methodMap.get(methodName)
										.invoke(vo)) {
									continue;
								} else if (methodMap.get(methodName).invoke(vo)
										.toString().equals("0")) {
									continue;
								}
							}
						}
						if (clazz
								.getName()
								.equals("com.bj58.groupbuy.action.model.GBReportGBCate")) {
							if (null == methodMap.get(methodName).invoke(vo)) {
								value = "";
							} else {
								DecimalFormat df = new DecimalFormat("0.00");
								String temp = methodMap.get(methodName)
										.invoke(vo).toString();
								if (temp.contains(".") && !temp.contains("%")) {
									value = df.format(Double.valueOf(temp));
									if (methodName.equals("getConversion_rate")) {
										value = value + "%";
									}
								} else {
									value = temp;
								}
							}
							cell.setCellValue(value);
							ind++;
							continue;
						}

						if (clazz.getName().equals(
								"com.bj58.groupbuy.action.model.ShopKpiDay")
								|| clazz.getName()
										.equals("com.bj58.groupbuy.action.model.ShopKpiHour")) {
							if (methodName.equals("getNewpvratio")) {
								break;
							}
						}

						if (clazz
								.getName()
								.equals("com.bj58.groupbuy.action.model.ShopProductStats")) {
							if (titles.length == 28) {
								if (methodName.equals("getPid")
										|| methodName.equals("getTitle")) {
									continue;
								}
							} else if (titles.length == 29) {
								if (methodName.equals("getDay")) {
									continue;
								}
							}
						}

						if (clazz
								.getName()
								.equals("com.bj58.groupbuy.action.model.PrecisionDataAll")) {
							try {
								if (null == methodMap.get(methodName)
										.invoke(vo)) {
									value = "";
								} else {
									value = methodMap.get(methodName)
											.invoke(vo).toString();
									// 百分比
									if (methodName.equals("getCTR_Channel")
											|| methodName.equals("getCTR_AD")
											|| methodName.equals("getPVR")
											|| methodName
													.equals("getMember_cost_zb")
											|| methodName
													.equals("getMember_cus_zb")) {
										isRate = 1;
									}
								}
								addExcelValue(filedCls, cell, value, isRate,wwb);
							} catch (Exception e) {
								e.printStackTrace();
							} finally {
								ind++;
								continue;
							}
						}

						if (clazz
								.getName()
								.equals("com.bj58.groupbuy.action.model.PrecisionDataDetail")
								|| clazz.getName()
										.equals("com.bj58.groupbuy.action.model.PrecisionDataPFCDetail")) {
							try {
								if (null == methodMap.get(methodName)
										.invoke(vo)) {
									value = "";
								} else {
									value = methodMap.get(methodName)
											.invoke(vo).toString();
									if (methodName.equals("getState")) {
										if ("0".equals(value)) {
											value = "成功";
										} else if ("1".equals(value)) {
											value = "初始化参与拍卖";
										} else if ("2".equals(value)) {
											value = "删除状态";
										} else if ("3".equals(value)) {
											value = "出价过低";
										} else if ("4".equals(value)) {
											value = "余额不足";
										} else if ("5".equals(value)) {
											value = "推广未通过审核";
										} else if ("6".equals(value)) {
											value = "用户被封禁";
										} else if ("7".equals(value)) {
											value = "频道被删除";
										}
									}
								}
								addExcelValue(filedCls, cell, value, isRate,wwb);
							} catch (Exception e) {
								e.printStackTrace();
							} finally {
								ind++;
								continue;
							}

						}

						if (clazz
								.getName()
								.equals("com.bj58.groupbuy.action.model.PrecisionDataYEBD")) {
							try {
								if (methodName.equals("getYh_cz")) {
									System.out.println("111");
								}
								if (null == methodMap.get(methodName)
										.invoke(vo)) {
									value = "";
								} else {
									value = methodMap.get(methodName)
											.invoke(vo).toString();
									if (methodName.equals("getJz_rate")) {
										isRate = 1;
									}
								}
								addExcelValue(filedCls, cell, value, isRate,wwb);
							} catch (Exception e) {
								e.printStackTrace();
							} finally {
								ind++;
								continue;
							}
						}

						if (clazz
								.getName()
								.equals("com.bj58.groupbuy.action.model.PrecisionDataPCDetail")
								|| clazz.getName()
										.equals("com.bj58.groupbuy.action.model.PrecisionDataCity")) {
							try {
								if (null == methodMap.get(methodName)
										.invoke(vo)) {
									value = "";
								} else {
									value = methodMap.get(methodName)
											.invoke(vo).toString();
								}
								addExcelValue(filedCls, cell, value, isRate,wwb);
							} catch (Exception e) {
								e.printStackTrace();
							} finally {
								ind++;
								continue;
							}
						}

						if (clazz.getName().equals(
								"com.bj58.groupbuy.action.model.ShopCheckStat")) {
							if (methodName.contains("Rate")) {
								if (null == methodMap.get(methodName)
										.invoke(vo)) {
									value = "";
								} else {
									DecimalFormat df = new DecimalFormat("0.00");
									value = df.format(Double.valueOf(methodMap
											.get(methodName).invoke(vo)
											.toString()) * 100)
											+ "%";
								}
								cell.setCellValue(value);
								ind++;
								continue;
							}
						}

						if (methodMap.containsKey(methodName)) {
							if (null == methodMap.get(methodName).invoke(vo)) {
								value = "";
							} else {
								value = methodMap.get(methodName).invoke(vo)
										.toString();
							}
						}
						cell.setCellValue(value);
						ind++;
					}
					rownum++;
				}
			}
			return wwb;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 计算换同比
	 * 
	 * @param groupBy_Values
	 * @param key
	 * @param cellValue
	 * @param gbCount
	 * @param index
	 * @param c
	 * @param r
	 * @param format
	 * @return
	 */
	public static String comRatio(HashMap<String, String> groupBy_Values,
			String key, String cellValue, int gbCount, int index, int c, int r) {
		// System.out.println(key+"----"+r+","+c+": "+cellValue);
		if (index < gbCount || "--".equals(cellValue))
			return "--";
		DecimalFormat df = new DecimalFormat("0.0000");
		String[] values = groupBy_Values.get(key) == null ? null
				: groupBy_Values.get(key).split(";");
		int realindex = index - gbCount;
		String ratio = "--";
		double todayV = Double.parseDouble(cellValue);
		if (values != null && realindex >= 0)
			if (realindex >= values.length
					|| "--".equals(values[realindex].split(",")[0]))
				ratio = "--";
			else {
				String tmp = values[realindex].split(",")[0];
				double result = Double.parseDouble(tmp);
				if (result > 0) {
					ratio = df.format((todayV - result) / result);
				}
			}
		return ratio;
	}

}

